﻿CREATE PROCEDURE [dbo].[proc_Loan_GetList_sp]
	(
		@CompanyId VARCHAR(500),	
		@DepartmentId INT,
		@EName VARCHAR(10),
		@StartDate nchar(10),
		@EndDate nchar(10),
		@StartIndex int,
		@EndIndex int,
		@flag int,
		@PZPerson nvarchar(50)
	)
AS
begin
	
	DECLARE @str VARCHAR(1000),@Sqlstr VARCHAR(2000)

	SET @str=''
	
	IF @CompanyId<>'' AND @CompanyId<>'0'
	BEGIN
		SET @str=' CompanyId in ('+@CompanyId+') and '
	END
	IF @DepartmentId<>0
	BEGIN
			SET @str=@str+' DepartmentId in (SELECT id FROM (
 SELECT ( SELECT	d.bmbh FROM	Department d WHERE id='+CONVERT(VARCHAR,@DepartmentId)+') AS a) AS nb1,Department 
WHERE LEFT(bmbh,LEN(a))=a) and '
	END
	IF @PZPerson<>''
	BEGIN
		SET @str=@str+' (PZPerson ='''+@PZPerson+''' or SHPerson ='''+@PZPerson+''') and '
	END
	
	IF @EName<>''
	BEGIN
		SET @str=@str+' ( EName like ''%'+@EName+'%''  or Summary like ''%'+@EName+'%'') and '
	END
	
	IF @StartDate<>'' and @EndDate<>''
	BEGIN
		SET @str=@str+' Convert(VARCHAR(10),lrrq,120) between '''+@StartDate+''' and '''+@EndDate+''' and '
	END
	IF @flag<>-1
	BEGIN
		SET @str=@str+' flag ='+CONVERT(VARCHAR, @flag)+' and '
	END
	
	IF @str<>''
	BEGIN
		set @str=' where '+SUBSTRING(@str,1,LEN(@str)-3)
	END
	
	SET @Sqlstr=' ;WITH list As( SELECT 
	  ROW_NUMBER() OVER (ORDER BY lrrq desc)AS Row
	  ,id,[CompanyId]
      ,[CompanyName]
      ,[DepartmentId]
      ,[DepartmentName]
      ,[ENameId]
      ,[EName]
      ,[PostCategoryId]
      ,[PostCategory]
      ,[Address]
      ,[Days]
      ,[Money]
      ,[Cause]
      ,[PZPerson]
      ,[PZPersonName]
      ,[SHPerson]
      ,[SHPersonName]
      ,[lrrq]
      ,[userid]
      ,[username]
      ,[Summary],flag FROM Loan '+@str +')
      
      select *,(select count(1) from list) as cid,(select sum(Money) from list) as zje from list where Row Between '+CONVERT(VARCHAR,@StartIndex)+' and '+CONVERT(VARCHAR,@EndIndex)+'
      '
	
	--PRINT(@Sqlstr)
	EXEC(@Sqlstr)
	
	
end			
	RETURN
